**by month
**Excess Deaths
import delimited "HSSC replication data\Public data\reference_hospitalization_all_locs.csv", clear
gen time=date(date,"YMD")
gen time_string=time
format time_string %td
tab time_string 
gen year=year(time)
gen month=month(time)
gen day=day(time)
tab year
tab day

keep if year==2021 & month==3 & day==31

gen ratio_actual_report=totdea_mean /totdea_reported_mean

sum ratio_actual_repor totdea_reported_mean totdea_mean 

ren totdea_reported_mean_p100k_rate totdea_reported_mean_p100k

foreach x in total_pop totdea_mean_p100k_rate  totdea_reported_mean_p100k seroprev_mean mobility_composite ///
ratio_actual_report totdea_mean totdea_reported_mean {
ren `x' IHME`x'
}

ren location_name countryname
ren location_id ihme_id

merge m:1 countryname using "G:\World_Poll\People\Jonathan_Rothwell\WP\WP COVID\Ext_Data\ihme_country_codes.dta", update
replace countrycode="CIV" if ihme_id==205
replace countrycode="YEM" if ihme==157
drop if _merge==2
drop _merge
drop if ihme==35
encode countrycode, gen(id)
drop if id==.


ren IHMEseroprev_mean IHME_sero
ren IHMEtotdea_mean_p100k_rate IHME_est_deaths
ren IHMEtotdea_reported_mean_p100k IHME_reported_deaths


collapse (first) ihme_id countryname (mean) IHMEtotdea_mean	IHMEtotdea_reported_mean	IHMEmobility_composite	IHMEtotal_pop	IHME_est_deaths	IHME_reported_deaths	IHME_sero	IHMEratio_actual_report, by(countrycode)

save "HSSC replication data\Public data\IHME_excess_deaths_through_March_2021.dta", replace

